﻿using ExcelHelper;
using ExcelHelper.ExcelModel;
using Microsoft.Extensions.DependencyInjection;
using SqlSugar;
using SqlSugarAndEntity;
using System;
using System.Collections.Generic;
using System.Diagnostics.CodeAnalysis;
using System.Dynamic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace UpdateDatabase
{
    public class ImportCommonModule
    {
        protected IBaseMethod SqlSugarProvider;
        public DateTime ExecuteDate;
        public ImportCommonModule()
        {
            IServiceCollection services = new ServiceCollection();
            services.AddSingleton<IBaseMethod, BaseMethod>();
            IServiceProvider serviceProvider = services.BuildServiceProvider();
            SqlSugarProvider = serviceProvider.GetService<IBaseMethod>();
            ExecuteDate = DateTime.Now;
        }
        /// <summary>
        /// 插入作者信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="list"></param>
        protected virtual void InsertAuthorInfo<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            //去重获取excel里面的所有作者信息
            var ExcelAuthors = list
                .GroupBy(t => new { t.Teacher })
                .Select(t => new authors
                {
                    Name = t.First().Teacher,
                    AuthorCode = CheckStringChinese(t.First().Teacher) ? $"{NPinyin.Pinyin.GetPinyin(t.First().Teacher) + DateTime.Now.ToString("yyyyMMdd")}".Replace(" ", "") : t.First().Teacher,
                    Infos = t.First().TeachInfo,
                    AuthorImage = t.First().TeacherPicture,
                    AddDateTime = ExecuteDate
                })
                .ToList();
            var x = db.Storageable(ExcelAuthors).WhereColumns(t => new { t.AuthorImage }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        public static bool CheckStringChinese(string text)
        {
            bool res = false;
            foreach (char t in text)
            {
                if ((int)t > 127)
                    res = true;
            }
            return res;
        }
        /// <summary>
        /// 插入课程信息
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list</param>
        protected virtual void InsertCourseInfo<T>(ISqlSugarClient db, List<T> list) where T :BaseExcelModel
        {
            List<courses> courses = list
                .GroupBy(t => new { t.CourseName, t.CoursePicture, t.CourseInfo })
                .Select(t => new courses
                {
                    Name = t.FirstOrDefault().CourseName,
                    CourseImage = t.FirstOrDefault().CoursePicture,
                    Infos = t.FirstOrDefault().CourseInfo,
                    CreateTime = ExecuteDate,
                    EditDateTime = ExecuteDate,
                    Status = 0,
                    KeyWords = t.FirstOrDefault().KeyWords
                }).ToList();
            var x = db.Storageable(courses).WhereColumns(t => new { t.CourseImage }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        /// <summary>
        /// 对excel解析出来的数据进行变更，把需要变更为id的字段进行替换，提升后面的效率
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">直接解析出来的数据</param>
        /// <returns>id替换之后的数据</returns>
        protected virtual List<T> ExchangeModelInfo<T>(ISqlSugarClient db, List<T> list,int MaxLevel,int ProductId,int CataType=0 ) where T : BaseExcelModel
        {
            
            var CourseImgs = list.Select(t => t.CoursePicture);
            //获取EXCEL内全部的课程
            List<courses> courses = db.Queryable<courses>().Where(t => CourseImgs.Contains(t.CourseImage)).ToList();
            var AuthorImgs = list.Select(t => t.TeacherPicture);
            var AuthorList = db.Queryable<authors>().Where(t=> AuthorImgs.Contains(t.AuthorImage)).ToList();
            var CataList = db.Queryable<catalogs>().Where(t => t.ProductId == ProductId).ToList();
            list.ForEach(t =>
            {
                //把课程名称变成ID
                t.CourseID = courses.Where(a => a.CourseImage == t.CoursePicture).Select(a => a.Id).FirstOrDefault();
                //把讲师名称替换成ID
                t.AuthorID = AuthorList.Where(a => a.AuthorImage == t.TeacherPicture).Select(a => a.Id).FirstOrDefault();
                //把最后一级目录名称替换成ID
                //把三级目录名称替换成ID
                CataList = CreateCatalog(db, CataList, t, MaxLevel, ProductId, CataType);
                t.CatalogID = CataList.Where(a => 
                {
                    
                    var CataValue = typeof(T).GetProperty($"Level{MaxLevel}").GetValue(t).ToString();
                    if (CataType != 0)
                    {
                        return a.Name == CataValue && a.Level == MaxLevel && a.CatalogType == CataType;
                    }
                    else
                    {
                        return a.Name == CataValue && a.Level == MaxLevel;
                    }
                    
                })
                .Select(t=>t.Id)
                .FirstOrDefault();
            });
            return list;
        }

        /// <summary>
        /// 插入视频数据,并将数据源list的videoid修改
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换过id后的list</param>
        protected virtual List<T> InsertVideo<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            List<videos> videos = list.
                GroupBy(t => new
                {
                    t.VideoCode,
                    t.VideoPicture,
                }).
                Select(t =>
                {
                    if (t.FirstOrDefault().Duration == null||!double.TryParse(t.FirstOrDefault().Duration, out double value))
                    {
                        value = 0;
                    }
                    if(t.FirstOrDefault().VideoSize ==null || !double.TryParse(t.FirstOrDefault().VideoSize.Replace("MB",""),out double videosize))
                    {
                        videosize = 0;
                    }
                    return new videos
                    {
                        VideoId = t.FirstOrDefault().ViedoId,
                        ProperTile = t.FirstOrDefault().TitleName,
                        VideoCode = t.FirstOrDefault().VideoCode,
                        VideoImage = t.FirstOrDefault().VideoPicture,
                        VideoInfo = t.FirstOrDefault().KeyWords,
                        Duration = value,
                        ResolutionRatio = t.FirstOrDefault().Resolving,
                        ProductFrom = t.FirstOrDefault().SourceFrom,
                        AuthorId = t.FirstOrDefault().AuthorID,
                        CreateTime = ExecuteDate,
                        LastUpdateTime = ExecuteDate,
                        PublishTime = ExecuteDate,
                        KeyWords = t.FirstOrDefault().KeyWords,
                        FileSize=videosize,
                    };
                }).ToList();
            var s = db.Storageable(videos).WhereColumns(t => new { t.VideoCode }).ToStorage();
            s.AsInsertable.ExecuteCommand();
            //把视频主键ID全部改为videoid
            //var sql = db.Updateable<videos>().SetColumns(t => t.Id == t.VideoId).Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ExecuteCommand();
            var videosCodes = videos.Select(t => t.VideoCode);
            videos = db.Queryable<videos>().Where(t => t.VideoCode.Contains(t.VideoCode)).ToList();
            list.ForEach(t =>
            {
                t.ViedoId = videos.Where(a => a.VideoCode == t.VideoCode).Select(a => a.Id).FirstOrDefault();
            });
            return list;
        }
        /// <summary>
        /// 插入视频和课程之间的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list数据</param>
        protected virtual void InsertVideo_r_Course<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            //获取今天新插入的课程信息
            //List<courses> courses = baseMethod.Db().Queryable<courses>().Where(t => SqlFunc.DateIsSame(t.CreateTime, DateTime.Now)).ToList();
            List<video_r_course> vrc = list.
                GroupBy(t => new
                {
                    t.ViedoId,
                    t.CourseID,
                }).
                Select(t => new video_r_course
                {
                    VideoId = t.FirstOrDefault().ViedoId,
                    CourseId = t.FirstOrDefault().CourseID,
                    VideoTitle = t.FirstOrDefault().TitleName,
                    CreateTime = ExecuteDate,
                    EditDateTime = ExecuteDate,
                    Status = 0,
                }).ToList();
            var s = db.Storageable(vrc).WhereColumns(t => new { t.VideoId, t.CourseId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
            //db.Insertable(vrc).ExecuteCommand();
            db.Updateable<video_r_course>().SetColumns(t => t.Order == t.Id).Where(t => SqlFunc.DateIsSame(t.CreateTime, ExecuteDate)).ExecuteCommand();
        }
        /// <summary>
        /// 插入课程和目录的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        /// <param name="list">变换后的list数据</param>
        protected virtual void InsertCourse_r_catalog<T>(ISqlSugarClient db, List<T> list) where T : BaseExcelModel
        {
            List<course_r_catalog> crc = list.
                GroupBy(t => new
                {
                    t.CatalogID,
                    t.CourseID
                }).
                Select(t => new course_r_catalog
                {
                    CourseId = t.FirstOrDefault().CourseID,
                    CatalogId = t.FirstOrDefault().CatalogID,
                    EditDateTime = ExecuteDate,
                    PublishTime = ExecuteDate,
                    Year = ExecuteDate.Year.ToString(),
                    Status = 0
                }).ToList();
            var s = db.Storageable(crc).WhereColumns(t => new { t.CourseId, t.CatalogId }).ToStorage();
            s.AsInsertable.ExecuteCommand();
        }

        /// <summary>
        /// 插入课程和产品之间的关系
        /// </summary>
        /// <param name="db">sugar实例</param>
        protected void InsertCourse_r_product(ISqlSugarClient db,int productId)
        {
            var crp = db.Queryable<courses>()
                .Where(t => SqlFunc.Subqueryable<course_r_product>().Where(a => a.CourseId == t.Id).NotAny())
                .Distinct()
                .Select(t => new course_r_product()
                {
                    CourseId = t.Id,
                    ProductId = productId,
                    Status = 0,
                    EditDateTime = SqlFunc.ToDate(ExecuteDate)
                }).ToList();
            var x = db.Storageable(crp).WhereColumns(t => new { t.CourseId, t.ProductId }).ToStorage();
            x.AsInsertable.ExecuteCommand();
        }
        /// <summary>
        /// 检查excel元素是否正确
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="MaxLevel"></param>
        /// <returns></returns>
        public (bool Result, string ErrorMsg) CheckExcelData<T>(List<T> list,int MaxLevel) where T : BaseExcelModel
        {
            var CheckVideo = ValidateVideoCodeAndVideoPic(list);
            if (!CheckVideo.Result)
            {
                return CheckVideo;
            }
            var CheckCourse = ValidateCourseNameAndCoursePic(list);
            if (!CheckCourse.Result)
            {
                return CheckCourse;
            }
            var CheckCatalog = ValidateCatalogAndCoursePic(list, MaxLevel);
            if (!CheckCatalog.Result)
            {
                return CheckCatalog;
            }
            return (true, "");
        }
        /// <summary>
        /// 检验一个videoCode是否对应一个videopic
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        private (bool Result,string ErrorMsg) ValidateVideoCodeAndVideoPic<T>(List<T> list) where T : BaseExcelModel
        {
            bool Result = true;
            string ErrorMsg = string.Empty;
            //excel总数据
            int TotalCount = list.Count;
            //检查video和videopic是否为唯一对应关系
            int VideoCount = list.GroupBy(t => new { t.VideoCode, t.VideoPicture }).Count();
            if (TotalCount != VideoCount)
            {
                //检查videocode和pic是不是存在不唯一对应关系
                var ErrorVideo = list
                    .Where(a => list.Where(b => (a.VideoCode == b.VideoCode && a.VideoPicture != b.VideoPicture) || (a.VideoCode != b.VideoCode && a.VideoPicture == b.VideoPicture)).Any())
                    .GroupBy(a => new { a.VideoCode, a.VideoPicture })
                    .Select(a => new { a.FirstOrDefault().VideoCode, a.FirstOrDefault().VideoPicture });
                ErrorMsg += $"请检查excel，以下videocode和videopic匹配不唯一!";
                foreach (var item in ErrorVideo)
                {
                    Result = false;
                    ErrorMsg += $":{item.VideoCode}:{item.VideoPicture}";
                }
            }
            return (Result,ErrorMsg);
        }
        /// <summary>
        /// 检验一个课程名是否对应一个课程图片
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns></returns>
        private (bool Result, string ErrorMsg) ValidateCourseNameAndCoursePic<T>(List<T> list) where T : BaseExcelModel
        {
            bool Result = true;
            string ErrorMsg = string.Empty;
            var ErrorCourse = list
                .Where(a => list.Where(b => (b.CourseName == a.CourseName && b.CoursePicture != a.CoursePicture) || (b.CourseName != a.CourseName && b.CoursePicture == a.CoursePicture)).Any())
                .GroupBy(a => new { a.CourseName, a.CoursePicture })
                .Select(a => new { a.FirstOrDefault().CourseName, a.FirstOrDefault().CoursePicture });
            if (ErrorCourse.Any())
            {
                ErrorMsg = $"请检查excel，以下课程名称和课程图片匹配不唯一!";
                foreach (var item in ErrorCourse)
                {
                    Result = false;
                    ErrorMsg += $": {item.CourseName}:{item.CoursePicture}";
                }
            }
            return (Result, ErrorMsg);
        }
        /// <summary>
        /// 检验一个课程是否对应一个目录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <param name="MaxLevel"></param>
        /// <returns></returns>
        private (bool Result, string ErrorMsg) ValidateCatalogAndCoursePic<T>(List<T> list,int MaxLevel) where T : BaseExcelModel
        {
            string MaxLevelStr = $"Level{MaxLevel}";
            bool Result = true;
            string ErrorMsg = string.Empty;
            var Selector = ExpressionHelper.CreateSelectExpression<T, GroupCataAndCourseModel>(
                new Dictionary<string, string>()
                {
                    { MaxLevelStr,"LevelMax" },
                    { "CoursePicture","CoursePicture" }
                });
            //检查一个课程下是不是只有一个分类
            var GroupLevel = list.Select(Selector.Compile()).Distinct(new Compare());
            foreach (var course in GroupLevel)
            {
                var select = GroupLevel
                    .Where(t => t.CoursePicture == course.CoursePicture);
                if (select.Count() > 1)
                {
                    foreach (var item in select)
                    {
                        ErrorMsg += $"请检查excel，以下课程图片和分类匹配不唯一！:{item.LevelMax}:{item.CoursePicture}";
                    }
                }
            }
            return (Result, ErrorMsg);
        }
        protected int GetInsertDataTotal(ISqlSugarClient db,int level,int ProductId)
        {
            var count = db.Queryable<videos, authors, video_r_course, courses, course_r_catalog, catalogs, course_r_product>(
                (a, b, c, d, e, f, g) => new JoinQueryInfos(
                  JoinType.Inner, a.AuthorId == b.Id,
                  JoinType.Inner, c.VideoId == a.Id,
                  JoinType.Inner, c.CourseId == d.Id,
                  JoinType.Inner, d.Id == e.CourseId,
                  JoinType.Inner, e.CatalogId == f.Id && f.Level == level,
                  JoinType.Inner, d.Id == g.CourseId && g.ProductId == ProductId
                ))
                .Where((a, b, c, d, e, f, g) => SqlFunc.DateIsSame(a.CreateTime.Value,ExecuteDate) )
                .Count();
            return count;
        }
        /// <summary>
        /// 根据缺失的目录插入相应等级的目录层级
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="db"></param>
        /// <param name="MaxLevel"></param>
        /// <param name="ProductId"></param>
        /// <param name="CataType"></param>
        /// <param name="catalogs"></param>
        /// <param name="item"></param>
        private List<catalogs> CreateCatalog<T>(ISqlSugarClient db,List<catalogs> catalogs, T item, int MaxLevel, int ProductId, int CataType=0)
            where T : BaseExcelModel
        {
            var catalast = catalogs
                    .Where(a =>
                    {
                        var prop = item.GetType().GetProperty($"Level{MaxLevel}");
                        var value = prop.GetValue(item).ToString();
                        if (CataType != 0)
                        {
                            return a.Name == value && a.ProductId == ProductId && a.Level.Value == MaxLevel&&a.CatalogType==CataType;
                        }
                        else
                        {
                            return a.Name == value && a.ProductId == ProductId && a.Level.Value == MaxLevel;
                        }
                       
                    })
                    .Select(a => a.Id.ToString()).FirstOrDefault();
            if (catalast == null)
            {
                //最后一级没找到就从第一级开始找目录
                for (int i = 1; i <= MaxLevel; i++)
                {
                    var prop = item.GetType().GetProperty($"Level{i}");
                    var value = prop.GetValue(item).ToString();
                    var ParentCata = catalogs.Where(a =>
                    {
                        if (CataType != 0)
                        {
                            return a.Name == value && a.ProductId == ProductId && a.Level.Value == i && a.CatalogType == CataType;
                        }
                        else
                        {
                            return a.Name == value && a.ProductId == ProductId && a.Level.Value == i;
                        }
                    }).FirstOrDefault();
                    if (ParentCata == null)
                    {
                        var cata = new catalogs()
                        {
                            Level = i,
                            Name = value,
                            ParentId = i == 1 ? 0 : catalogs.Where(a => a.Name == item.GetType().GetProperty($"Level{i - 1}").GetValue(item).ToString()).Select(t => t.Id).FirstOrDefault(),
                            ProductId = ProductId,
                            CatalogType =CataType,
                            CreateTime = ExecuteDate,
                            Status = 0,
                            IsTop = false,
                            EditDateTime = ExecuteDate,
                        };
                        var Insertitem = db.Insertable(cata).ExecuteReturnEntity();
                        catalogs.Add(Insertitem);
                    }
                }
            }
            return catalogs;
        }
    }


}
